﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GenericTools;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;
using ActiveDatabaseSoftware.ActiveQueryBuilder;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using DataModels.Enumerations;
using DataModels.Common;
using GenericTools.Models;
using GenericTools.CustomReporting;
using System.Text.RegularExpressions;
using ReportingManagement.Components;
using System.Windows.Interop;
using ReportingManagement.Components.Queries;

namespace ReportingManagement
{
    public partial class MainWindow
    {
        private const int VIEW = 1;

        private bool tabPageQueriesLoaded = false;
        private DataTable queryDataTable = null;
        private DbConnection queryingDBConnection = null;

        private void tabPageQueries_Enter(object sender, EventArgs e)
        {
            if (!tabPageQueriesLoaded)
            {
                // Set up event handler for the embeded user control in this tab
                var queryManager = elementHostQueryManager.Child as QueryManagement;
                if (queryManager != null)
                {
                    queryManager.StatusChanged += ToolStripStatusLabel_StatusChanged;
                }

                if (AppConfiguration.AccessMethod != DataAccessMethod.DIRECT_ACCESS)
                {
                    #region Check to see if the cache database really exists! If not create it
                    try
                    {
                        DbCommand command = GenericDataAccess.CreateCommand(AppConfiguration.DBProviderName,
                            AppConfiguration.SQLCacheDBConnection.Replace(AppConfiguration.CacheDBName, "master"));
                        command.CommandText = "SELECT count(*) FROM master.dbo.sysdatabases WHERE name = @DatabaseName";
                        DbParameter param = command.CreateParameter();
                        param.ParameterName = "@DatabaseName";
                        param.DbType = DbType.String;
                        param.Value = AppConfiguration.CacheDBName;
                        command.Parameters.Add(param);

                        string output = null;
                        output = GenericDataAccess.ExecuteScalar(command, false);                        
                        int count = Int32.Parse(output);
                        if (count == 1)
                        {
                            // Cache DB exists check the date
                            DateTime? cacheCreationDate = Utilities.GetOfflineDataDate();
                            TimeSpan timeSpan = cacheCreationDate.Value - DateTime.Now;
                            if (timeSpan.Days > Properties.Settings.Default.OfflineDBCheckingDays)
                            {
                                DialogResult response = MessageBox.Show(this,
                                String.Format("The offline database found is {2} days old! The data may have changed over that period of time{0}{0}{1} " +
                                    "will try to download most recent and recreate it. This may take several minutes. {0}{0}Do you want to continue?",
                                    Environment.NewLine, AppConfiguration.ApplicationName, Properties.Settings.Default.OfflineDBCheckingDays),
                                AppConfiguration.ApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question);                                
                                
                                if (response == System.Windows.Forms.DialogResult.Yes)
                                {
                                    bool okToContinue = DownloadingData.LoadingDBIntoSQLServer(this);
                                    if (okToContinue)
                                    {
                                        MessageBox.Show(this,
                                            "Cache database has been created successfully. You can now continue.",
                                            AppConfiguration.ApplicationName,
                                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                    else
                                    {
                                        MessageBox.Show(this,
                                            "Cache database could not be created. Check your settings for local cache database.",
                                            AppConfiguration.ApplicationName,
                                            MessageBoxButtons.OK, MessageBoxIcon.Hand);
                                    }
                                }
                            }
                        }
                        else
                        {
                            tabQueries.Enabled = false; // Disable the entire query tab
                            // Cache DB does not exist, prompt to create one
                            DialogResult response = MessageBox.Show(this,
                                String.Format("Could not find the cache database!{0}{0}{1} " +
                                    "will try to recreate it. This may take several minutes.{0}{0}Do you want to continue?",
                                    Environment.NewLine, AppConfiguration.ApplicationName),
                                AppConfiguration.ApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                            if (response == System.Windows.Forms.DialogResult.Yes)
                            {
                                bool okToContinue = DownloadingData.LoadingDBIntoSQLServer(this);
                                if (okToContinue)
                                {
                                    MessageBox.Show(this,
                                        "Cache database has been created successfully. You can now continue.",
                                        AppConfiguration.ApplicationName,
                                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    tabQueries.Enabled = true;
                                }
                                else
                                {
                                    MessageBox.Show(this,
                                        "Cache database could not be created. Check your settings for local cache database.",
                                        AppConfiguration.ApplicationName,
                                        MessageBoxButtons.OK, MessageBoxIcon.Hand);
                                }
                            }
                            else
                            {
                                tabPageQueriesLoaded = false;
                                return;
                            }
                        }
                        
                    }
                    catch (Exception ex)
                    {
                        DisplayErrorMessage(ex);
                    }
                    #endregion
                }

                Querying.DataProvider = AppConfiguration.DBProviderName;
                Querying.ConnectionString = AppConfiguration.SQLCacheDBConnection;

                #region Initializing cache database
                try
                {
                    toolStripStatusLabel.Text = "Initializing local database connections, Please wait...";
                    Cursor.Current = Cursors.WaitCursor;
                    Application.DoEvents();

                    DbCommand command = null;
                    switch (AppConfiguration.AccessMethod)
                    {
                        case DataAccessMethod.DIRECT_ACCESS:
                            command = GenericDataAccess.CreateCommand();
                            queryingDBConnection = command.Connection;
                            MSSQLMetadataProvider.Connection = command.Connection;
                            if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                            queryBuilder.MetadataProvider = MSSQLMetadataProvider;
                            queryBuilder.SyntaxProvider = MSSQLSyntaxProvider;

                            PresentAppropriateDBTables();
                            queryBuilder.RefreshMetadata();
                            //queryBuilder.LoadMetadata(command.Connection.Database);
                            break;

                        case DataAccessMethod.OFFLINE_DATA_ACCESS:
                            command = GenericDataAccess.CreateCacheDBCommand();
                            queryingDBConnection = command.Connection;
                            OLEDBMetadataProvider.Connection = command.Connection;
                            queryBuilder.MetadataProvider = OLEDBMetadataProvider;
                            queryBuilder.SyntaxProvider = MSAccessSyntaxProvider;

                            PresentAppropriateDBTables();
                            queryBuilder.RefreshMetadata();
                            break;

                        case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                            string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
                            if (AppConfiguration.DBConnectionString != cacheDBConnection) AppConfiguration.DBConnectionString = cacheDBConnection;
                            command = GenericDataAccess.CreateCommand();
                            queryingDBConnection = command.Connection;
                            MSSQLMetadataProvider.Connection = command.Connection;
                            queryBuilder.MetadataProvider = MSSQLMetadataProvider;
                            queryBuilder.SyntaxProvider = MSSQLSyntaxProvider;

                            PresentAppropriateDBTables();
                            queryBuilder.RefreshMetadata();
                            //queryBuilder.LoadMetadata("IQSMS_CacheDB");
                            break;

                        case DataAccessMethod.WEB_SERVICES:
                            if (AppConfiguration.CacheDBType == CacheDB.MS_ACCESS)
                            {
                                command = GenericDataAccess.CreateCacheDBCommand();
                                queryingDBConnection = command.Connection;
                                OLEDBMetadataProvider.Connection = command.Connection;
                                queryBuilder.MetadataProvider = OLEDBMetadataProvider;
                                queryBuilder.SyntaxProvider = MSAccessSyntaxProvider;

                                PresentAppropriateDBTables();
                                queryBuilder.RefreshMetadata();
                            }

                            if (AppConfiguration.CacheDBType == CacheDB.SQL_SERVER)
                            {
                                string cacheDBConnection2 = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
                                if (AppConfiguration.DBConnectionString != cacheDBConnection2) AppConfiguration.DBConnectionString = cacheDBConnection2;
                                command = GenericDataAccess.CreateCommand();
                                queryingDBConnection = command.Connection;
                                MSSQLMetadataProvider.Connection = command.Connection;
                                queryBuilder.MetadataProvider = MSSQLMetadataProvider;
                                queryBuilder.SyntaxProvider = MSSQLSyntaxProvider;

                                PresentAppropriateDBTables();
                                queryBuilder.RefreshMetadata();
                                //queryBuilder.LoadMetadata("IQSMS_CacheDB");
                            }
                            break;
                    }

                    IDbConnection connection = (DbConnection)queryBuilder.MetadataProvider.Connection;
                    Querying.DataProvider = AppConfiguration.DBProviderName;
                    Querying.ConnectionString = AppConfiguration.SQLCacheDBConnection;

                    toolStripStatusLabel.Text = "Ready";
                    Cursor.Current = Cursors.Default;
                    tabPageQueriesLoaded = true;
                }
                catch (Exception ex)
                {
                    DisplayErrorMessage(ex);
                }
                #endregion              
            }
        }

        private void SqlTextEditor_Leave(object sender, EventArgs e)
        {
            try
            {
                queryBuilder.SQL = sqlTextEditor.Text;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Parsing error");
            }
        }

        private void plainTextSQLBuilder_SQLUpdated(object sender, EventArgs e)
        {
            sqlTextEditor.Text = plainTextSQLBuilder.SQL;
        }

        private void tabPagePreview_Enter(object sender, EventArgs e)
        {
            toolStripStatusLabel.Text = "Evaluating data from query, Please wait...";
            Cursor.Current = Cursors.WaitCursor;
            Application.DoEvents();

            if (String.IsNullOrEmpty(queryBuilder.SQL))
            {
                lblRecordCount.Text = "No results";
                btnExportToExcel.Enabled = false;
                btnRunOnRemoteServer.Enabled = false;
                btnSaveQuery.Enabled = false;

                queryDataTable = null;
                dataGridView1.DataSource = null;
                dataGridView1.Refresh();
            }
            else
            {
                IDbConnection connection = queryBuilder.MetadataProvider.Connection;
                IDbCommand command = connection.CreateCommand();
                command.CommandText = queryBuilder.SQL;

                if (queryBuilder.Parameters.Count > 0)
                {
                    DataTable dataTable = new DataTable();
                    DataColumn column = new DataColumn("ParameterName", Type.GetType("System.String"));
                    dataTable.Columns.Add(column);
                    column = new DataColumn("DataType", DbType.Object.GetType());
                    dataTable.Columns.Add(column);
                    column = new DataColumn("Value", Type.GetType("System.String"));
                    dataTable.Columns.Add(column);
                    
                    ActiveDatabaseSoftware.ActiveQueryBuilder.Parameter parameter = null;
                    for (int i = 0; i < queryBuilder.Parameters.Count; i++)
                    {
                        parameter = queryBuilder.Parameters[i];
                        bool parameterNotFound = true;
                        foreach (DataRow r in dataTable.Rows)
                        {
                            string param = r[0].ToString();
                            if (param == parameter.Name)
                            {
                                parameterNotFound = false;
                                DbType storedDataType = (DbType)r[1];
                                if (parameter.DataType != DbType.Object && storedDataType == DbType.Object)
                                {
                                    r[1] = parameter.DataType;
                                    break;
                                }
                            }
                        }

                        if (parameterNotFound)
                        {
                            DataRow row = dataTable.NewRow();
                            row[0] = parameter.Name;
                            row[1] = parameter.DataType;
                            row[2] = "Click to insert data...";
                            dataTable.Rows.Add(row);
                        }
                    }

                    QueryParameters queryParameters = new QueryParameters(dataTable);
                    DialogResult response = queryParameters.ShowDialog(this);
                    if (response == System.Windows.Forms.DialogResult.OK)
                    {
                        foreach (DataRow r in queryParameters.Parameters.Rows)
                        {
                            string parameterName = String.Format("@{0}", r[0]);
                            DbType dbType = (DbType)r[1];
                            object value = r[2];

                            IDataParameter param = command.CreateParameter();
                            param.ParameterName = parameterName;
                            param.DbType = dbType;
                            param.Value = value;
                            command.Parameters.Add(param);
                        }
                    }
                    else return;
                }

                try
                {
                    IDataReader reader = command.ExecuteReader();
                    queryDataTable = new DataTable();
                    queryDataTable.Load(reader);
                    reader.Close();

                    dataGridView1.DataSource = queryDataTable;
                    dataGridView1.Refresh();
                    lblRecordCount.Text = String.Format("Record Count: {0}", queryDataTable.Rows.Count);
                    btnExportToExcel.Enabled = true;
                    btnSaveQuery.Enabled = true;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DATA_ACCESS)
                        || (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS)) btnRunOnRemoteServer.Enabled = false;
                    else btnRunOnRemoteServer.Enabled = true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(this,
                        String.Format("The result cannot be displayed.{0}{0}{1}", Environment.NewLine, ex.Message),
                        AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }                
            }

            toolStripStatusLabel.Text = "Ready";
            Cursor.Current = Cursors.Default;
            Application.DoEvents();
        }

        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            CreateExcelExportFile(queryDataTable);
        }

        private void PresentAppropriateDBTables()
        {
            MetadataFilterItem mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ReportData";
            mfi.ObjectMaskCaseSensitive = true;

            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ErrMsgSMS";
            mfi.ObjectMaskCaseSensitive = true;

            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ExcelTemplateCells";
            mfi.ObjectMaskCaseSensitive = true;

            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ExcelTemplateReport";
            mfi.ObjectMaskCaseSensitive = true;
            
            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ExcelTemplateSheets";
            mfi.ObjectMaskCaseSensitive = true;

            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "ImportTables";
            mfi.ObjectMaskCaseSensitive = true;

            mfi = queryBuilder.MetadataFilter.Add();
            mfi.Exclude = true;
            mfi.ObjectMask = "UserType";
            mfi.ObjectMaskCaseSensitive = true;
        }               

//        public DataTable RunQuery(int queryId, bool useRemoteServer)
//        {
//            try
//            {
//                toolStripStatusLabel.Text = "Evaluating data from query, Please wait...";
//                Cursor.Current = Cursors.WaitCursor;
//                Application.DoEvents();

//                IDbConnection connection = queryBuilder.MetadataProvider.Connection;
//                IDbCommand command = connection.CreateCommand();
//#if USING_ENTITIES
//                Models.EntityContext context = Models.EntityContext.DefaultContext;
//                var result = from query in context.Queries
//                             where query.Id == queryId
//                             select query;
//                Models.Query _Query = (Models.Query)result.Single();
//                int queryType = _Query.QueryType.Id;
//                string queryName = _Query.InternalName;
//                string definition = _Query.Definition;
//                if (_Query.QueryType.Name == Models.QueryType.VIEW) definition = String.Format("SELECT * FROM {0}", queryName);
//#else
//                DataTable queryDetails = Querying.GetQueryDetails(queryId);
//                DataRow data = queryDetails.Rows[0];
//                int queryType = Int32.Parse(data["QueryTypeId"].ToString());
//                string queryName = data["InternalName"].ToString();
//                string definition = data["Definition"].ToString();
//                if (queryType == VIEW) definition = String.Format("SELECT * FROM {0}", queryName); 
//#endif

//                queryBuilder.SyncSQL = definition;
//                command.CommandText = definition;
//                command.CommandType = CommandType.Text;

//                if (queryBuilder.Parameters.Count > 0)
//                {
//                    DataTable dataTable = new DataTable();
//                    DataColumn column = new DataColumn("ParameterName", Type.GetType("System.String"));
//                    dataTable.Columns.Add(column);
//                    column = new DataColumn("DataType", DbType.Object.GetType());
//                    dataTable.Columns.Add(column);
//                    column = new DataColumn("Value", Type.GetType("System.String"));
//                    dataTable.Columns.Add(column);

//                    ActiveDatabaseSoftware.ActiveQueryBuilder.Parameter parameter = null;
//                    for (int i = 0; i < queryBuilder.Parameters.Count; i++)
//                    {
//                        parameter = queryBuilder.Parameters[i];
//                        bool parameterNotFound = true;
//                        foreach (DataRow r in dataTable.Rows)
//                        {
//                            string param = r[0].ToString();
//                            if (param == parameter.Name)
//                            {
//                                parameterNotFound = false;
//                                DbType storedDataType = (DbType)r[1];
//                                if (parameter.DataType != DbType.Object && storedDataType == DbType.Object)
//                                {
//                                    r[1] = parameter.DataType;
//                                    break;
//                                }
//                            }
//                        }

//                        if (parameterNotFound)
//                        {
//                            DataRow row = dataTable.NewRow();
//                            row[0] = parameter.Name;
//                            row[1] = parameter.DataType;
//                            row[2] = "Click to insert data...";
//                            dataTable.Rows.Add(row);
//                        }
//                    }

//                    QueryParameters queryParameters = new QueryParameters(dataTable);
//                    DialogResult response = queryParameters.ShowDialog(this);
//                    if (response == System.Windows.Forms.DialogResult.OK)
//                    {
//                        foreach (DataRow r in queryParameters.Parameters.Rows)
//                        {
//                            string parameterName = String.Format("@{0}", r[0]);
//                            DbType dbType = (DbType)r[1];
//                            object value = r[2];

//                            IDataParameter param = command.CreateParameter();
//                            param.ParameterName = parameterName;
//                            param.DbType = dbType;
//                            param.Value = value;
//                            command.Parameters.Add(param);
//                        }
//                    }
//                    else return null;
//                }

//                if (useRemoteServer) queryDataTable = Utilities.RunCommandOnRemoteServer(command);
//                else
//                {
//                    IDataReader reader = command.ExecuteReader();
//                    queryDataTable = new DataTable();
//                    queryDataTable.Load(reader);
//                    reader.Close();
//                }

//                toolStripStatusLabel.Text = "Ready";
//                Cursor.Current = Cursors.Default;
//                Application.DoEvents();
//                return queryDataTable;
//            }
//            catch (Exception ex)
//            {
//                toolStripStatusLabel.Text = "Error occurred while evaluating error";
//                MessageBox.Show(this, ex.Message, "Could not evaluate query", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//                return null;
//            }
            
//        }

        //public DataTable RunQuery(bool useRemoteServer)
        //{
        //    try
        //    {
        //        toolStripStatusLabel.Text = "Evaluating data from query, Please wait...";
        //        Cursor.Current = Cursors.WaitCursor;
        //        Application.DoEvents();

        //        IDbConnection connection = queryBuilder.MetadataProvider.Connection;
        //        IDbCommand command = connection.CreateCommand();
        //        command.CommandText = queryBuilder.SQL;
        //        command.CommandType = CommandType.Text;

        //        //queryBuilder.SyncSQL = queryDefinition;
        //        ////queryBuilder.SQL = definition;    // This does not update the changes on time
        //        //queryBuilder.Focus();
        //        //command.CommandText = queryDefinition;

        //        if (queryBuilder.Parameters.Count > 0)
        //        {
        //            DataTable dataTable = new DataTable();
        //            DataColumn column = new DataColumn("ParameterName", Type.GetType("System.String"));
        //            dataTable.Columns.Add(column);
        //            column = new DataColumn("DataType", DbType.Object.GetType());
        //            dataTable.Columns.Add(column);
        //            column = new DataColumn("Value", Type.GetType("System.String"));
        //            dataTable.Columns.Add(column);

        //            ActiveDatabaseSoftware.ActiveQueryBuilder.Parameter parameter = null;
        //            for (int i = 0; i < queryBuilder.Parameters.Count; i++)
        //            {
        //                parameter = queryBuilder.Parameters[i];
        //                bool parameterNotFound = true;
        //                foreach (DataRow r in dataTable.Rows)
        //                {
        //                    string param = r[0].ToString();
        //                    if (param == parameter.Name)
        //                    {
        //                        parameterNotFound = false;
        //                        DbType storedDataType = (DbType)r[1];
        //                        if (parameter.DataType != DbType.Object && storedDataType == DbType.Object)
        //                        {
        //                            r[1] = parameter.DataType;
        //                            break;
        //                        }
        //                    }
        //                }

        //                if (parameterNotFound)
        //                {
        //                    DataRow row = dataTable.NewRow();
        //                    row[0] = parameter.Name;
        //                    row[1] = parameter.DataType;
        //                    row[2] = "Click to insert data...";
        //                    dataTable.Rows.Add(row);
        //                }
        //            }

        //            QueryParameters queryParameters = new QueryParameters(dataTable);
        //            DialogResult response = queryParameters.ShowDialog(this);
        //            if (response == System.Windows.Forms.DialogResult.OK)
        //            {
        //                foreach (DataRow r in queryParameters.Parameters.Rows)
        //                {
        //                    string parameterName = String.Format("@{0}", r[0]);
        //                    DbType dbType = (DbType)r[1];
        //                    object value = r[2];

        //                    IDataParameter param = command.CreateParameter();
        //                    param.ParameterName = parameterName;
        //                    param.DbType = dbType;
        //                    param.Value = value;
        //                    command.Parameters.Add(param);
        //                }
        //            }
        //            else return null;
        //        }

        //        if (useRemoteServer) queryDataTable = Utilities.RunCommandOnRemoteServer(command);
        //        else
        //        {
        //            IDataReader reader = command.ExecuteReader();
        //            queryDataTable = new DataTable();
        //            queryDataTable.Load(reader);
        //            reader.Close();
        //        }

        //        toolStripStatusLabel.Text = "Ready";
        //        Cursor.Current = Cursors.Default;
        //        Application.DoEvents();
        //        return queryDataTable;
        //    }
        //    catch (Exception ex)
        //    {
        //        toolStripStatusLabel.Text = "Error occurred while evaluating error";
        //        MessageBox.Show(this, ex.Message, "Could not evaluate query", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        //        return null;
        //    }

        //}

        //public void CheckViewsAndTables()
        //{
        //    //DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, Querying.ConnectionString);
        //    //IDbConnection connection = queryBuilder.MetadataProvider.Connection;
        //    //IDbCommand command = connection.CreateCommand();
        //    /*SqlConnection connection = new SqlConnection(Querying.ConnectionString);
        //    SqlCommand command = new SqlCommand();
        //    command.Connection = connection;*/

        //    DbCommand command = GenericDataAccess.CreateCommand();
        //    command.CommandType = CommandType.Text;

        //    command.CommandText = "SELECT [Name],[InternalName],[Definition],[CreateTable],[QueryTypeId] FROM Queries";
        //    DataTable queriesDataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
        //    foreach (DataRow row in queriesDataTable.Rows)
        //    {
        //        int queryType = Int32.Parse(row["QueryTypeId"].ToString());
        //        if (queryType == VIEW)
        //        {
        //            string viewName = row["InternalName"].ToString();
        //            string viewDefinition = row["Definition"].ToString();

        //            command.CommandText = @"SELECT COUNT(*) FROM sys.views WHERE name=@ViewName";
        //            command.Parameters.Clear();
        //            DbParameter param = command.CreateParameter();
        //            param.ParameterName = @"ViewName";
        //            param.DbType = DbType.String;
        //            param.Value = viewName;
        //            command.Parameters.Add(param);
        //            string output = GenericDataAccess.ExecuteScalar(command, false);

        //            int count = Int32.Parse(output);
        //            string query = null;
        //            if (count == 1)
        //            {
        //                query = String.Format("DROP VIEW {0}", viewName);
        //                command.CommandText = query;
        //                command.Parameters.Clear();
        //                GenericDataAccess.ExecuteNonQuery(command, false);
        //            }
        //            query = String.Format("CREATE VIEW {0} AS {1}", viewName, viewDefinition);
        //            command.CommandText = query;
        //            command.Parameters.Clear();
        //            GenericDataAccess.ExecuteNonQuery(command, false);

        //            bool createTable = Boolean.Parse(row["CreateTable"].ToString());
        //            if (createTable)
        //            {
        //                command.CommandText = @"SELECT COUNT(*) FROM sys.tables WHERE name=@TableName";
        //                command.Parameters.Clear();
        //                param = command.CreateParameter();
        //                param.ParameterName = @"TableName";
        //                param.DbType = DbType.String;
        //                param.Value = String.Format("tbl_{0}",viewName);
        //                command.Parameters.Add(param);
        //                output = GenericDataAccess.ExecuteScalar(command, false);

        //                count = Int32.Parse(output);
        //                if (count == 0)
        //                {
        //                    bool created = Querying.CreateTableFromView(viewName, viewDefinition);
        //                    if (!created)
        //                    {
        //                        MessageBox.Show(this, String.Format("Could not create table {0}", param.Value), 
        //                            AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        //                    }
        //                }
        //            }
        //        }                
        //    }
        //}

        bool editingQuery = false;
        private void btnQuerySave_Click(object sender, EventArgs e)
        {
            #region Preliminary Checks
            bool addingQuery = (txtQueryInternalName.Tag == null) && txtQueryInternalName.Enabled;

            if (String.IsNullOrEmpty(txtQueryName.Text))
            {
                MessageBox.Show(this, "Please provide query name",
                    AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtQueryName.Focus();
                return;
            }

            if (String.IsNullOrEmpty(txtQueryDescription.Text))
            {
                MessageBox.Show(this, "Please provide query description",
                    AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtQueryDescription.Focus();
                return;
            }

            if (String.IsNullOrEmpty(txtQueryInternalName.Text))
            {
                MessageBox.Show(this, "Please provide query internal name",
                    AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtQueryInternalName.Focus();
                return;
            }
            //else
            //{
            //    try
            //    {
            //        bool okToContinue = Querying.QueryInternalNameAvailable(txtQueryInternalName.Text);
            //        if (!okToContinue && addingQuery)
            //        {
            //            MessageBox.Show(this, "Query internal name you selected is already in use. Please choose another one",
            //            AppConfiguration.ApplicationName,
            //            MessageBoxButtons.OK, MessageBoxIcon.Warning);
            //            txtQueryInternalName.Focus();
            //            return;
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        MessageBox.Show(this, ex.Message,
            //        AppConfiguration.ApplicationName,
            //        MessageBoxButtons.OK, MessageBoxIcon.Warning);
            //    }
            //}
            #endregion

            string name = txtQueryName.Text;
            string description = txtQueryDescription.Text;
            string definition = sqlTextEditorSaveQuery.Text;
            string internalName = txtQueryInternalName.Text;

            List<DataModels.Models.QueryParameter> parameters = new List<DataModels.Models.QueryParameter>();
            DataModels.Models.Query query = new DataModels.Models.Query
            {
                Name = name,
                Description = description,
                InternalName = internalName,
                Definition = definition,
                CreatedByUserId = this.authenticatedUser.ID,
                QueryTypeId = DataModels.Models.QueryType.PARAMETERIZED_VIEW
            };

            HashSet<string> detectedParameterNames = new HashSet<string>();
            string pattern = @"@\w(?<!\d)[\w'-]*";
            Regex regex = new Regex(pattern);
            MatchCollection matchedParameters = regex.Matches(definition);
            string[] parts = definition.Split(null as string[], StringSplitOptions.RemoveEmptyEntries);
            foreach (Match match in matchedParameters)
            {
                // Before we add the word, we'll check to satisfy ourself that
                // there is no appearance of the parameter that shows it was declared.
                bool promptingParameter = true;
                for (int i = 0; i < parts.Length; i++)
                {
                    string part = parts[i];
                    if (part.Equals(match.Value, StringComparison.OrdinalIgnoreCase))
                    {
                        // If declared, the word before it should indicate that
                        if ((i - 1) >= 0)
                        {
                            string supposedDeclarator = parts[i - 1];
                            if (supposedDeclarator.Equals("DECLARE", StringComparison.OrdinalIgnoreCase))
                            {
                                promptingParameter = false;
                                break;
                            }
                        }
                    }
                }
                if (promptingParameter) detectedParameterNames.Add(match.Value);
            }

            foreach (var param in detectedParameterNames)
            {
                var parameter = new DataModels.Models.QueryParameter 
                { 
                    Name = param,
                };
                parameters.Add(parameter);
            }

            bool? okToContinue = parameters.Count > 0 ? false : true;
            if (parameters.Count > 0)
            {
                QueryParametersCreator parameterDefinitionEditor = new QueryParametersCreator(parameters);
                WindowInteropHelper helper = new WindowInteropHelper(parameterDefinitionEditor);
                helper.Owner = this.Handle;

                okToContinue = parameterDefinitionEditor.ShowDialog();
            }
            
            if (okToContinue.HasValue && okToContinue.Value)
            {
                // For now we are supporting only the views based queries and parameterized expressions.
                // If the query does not have any parameter, then we will also create a view based on that.
                int status = ReportQuerying.STATUS_OK_TO_CONTINUE;
                try
                {
                    if (editingQuery)
                    {
                        status = ReportQuerying.UpdateQuery(query, parameters);
                        if (status == ReportQuerying.STATUS_OK_TO_CONTINUE)
                        {
                            var args = new QueryManagementArgs
                            {
                                ActionPerformed = QueryManagementArgs.Action.QUERY_UPDATED,
                                Target = query
                            };
                            QueryManagement.FireEvent(args);
                            MessageBox.Show(this, "Query Saved Successfully",
                                    AppConfiguration.ApplicationName,
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;
                        }
                    }
                    else
                    {
                        status = ReportQuerying.AddQuery(query, parameters);
                        if (status == ReportQuerying.STATUS_OK_TO_CONTINUE)
                        {
                            var args = new QueryManagementArgs
                            {
                                ActionPerformed = QueryManagementArgs.Action.QUERY_ADDED,
                                Target = query
                            };
                            QueryManagement.FireEvent(args);
                            MessageBox.Show(this, "Query Saved Successfully",
                                    AppConfiguration.ApplicationName,
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;
                        }
                    }
                }
                catch (Exception ex)
                {
                    DisplayErrorMessage(ex);
                    return;
                }

                if (status != ReportQuerying.STATUS_OK_TO_CONTINUE)
                {
                    string message = "Unknown error occured that prevents you from saving your changes";
                    switch (status)
                    {
                        case ReportQuerying.STATUS_NAME_ALREADY_EXISTS:
                            message = "Selected names is already in use. Pick another one";
                            break;

                        case ReportQuerying.STATUS_NAME_IS_MISSING:
                            message = "Query name is required";
                            break;

                        case ReportQuerying.STATUS_DEFINITION_IS_MISSING:
                            message = "Query is missing its definition";
                            break;
                    }

                    MessageBox.Show(message);
                    return;
                }
            }




//            try
//            {
                

//                if (definition.Contains("@"))   // Possibly function or query with parameter
//                {
//                    bool success = default(bool);
//#if USING_ENTITIES
//                    string sQueryType = String.Empty;
//                    if (definition.StartsWith("CREATE FUNCTION", StringComparison.OrdinalIgnoreCase)) sQueryType = Models.QueryType.FUNCTION;
//                    if (definition.StartsWith("CREATE PROCEDURE", StringComparison.OrdinalIgnoreCase)) sQueryType = Models.QueryType.STORED_PROCEDURE;
//                    Models.EntityContext context = Models.EntityContext.DefaultContext;
//                    Models.QueryType queryType = context.QueryTypes.Single(t => t.Name == sQueryType);

//                    if (addingQuery)
//                    {
//                        Models.Query query = new Models.Query();
//                        query.Name = name;
//                        query.Description = description;
//                        query.InternalName = internalName;
//                        query.QueryType = queryType;
//                        context.Queries.Add(query);
//                        success = context.SaveChanges() == 0 ? false : true;
//                        if (success) _QueryManagerAllQueries.Add(query);
//                    }
//                    else
//                    {
//                        if (_QueryManagerSelectedQuery == null)
//                        {
//                            Exception ex = new Exception("Error occured while setting selected query");
//                            DisplayErrorMessage(ex);
//                        }
//                        else
//                        {
//                            _QueryManagerSelectedQuery.Name = name;
//                            _QueryManagerSelectedQuery.Description = description;
//                            _QueryManagerSelectedQuery.InternalName = internalName;
//                            _QueryManagerSelectedQuery.Definition = definition;
//                            _QueryManagerSelectedQuery.QueryType = queryType;
//                            success = context.SaveChanges() == 0 ? false : true;
//                        }
//                    }
//#else
//                    TypeOfQuery typeOfQuery = TypeOfQuery.PARAMETERIZED_VIEW;
//                    if (definition.StartsWith("CREATE FUNCTION", StringComparison.OrdinalIgnoreCase)) typeOfQuery = TypeOfQuery.FUNCTION;
//                    if (definition.StartsWith("CREATE PROCEDURE", StringComparison.OrdinalIgnoreCase)) typeOfQuery = TypeOfQuery.STORED_PROCEDURE;

//                    if (addingQuery)
//                    {
//                        success = Querying.SaveQuery(name,
//                        description,
//                        definition,
//                        internalName,
//                        typeOfQuery,
//                        chbCreateTable.Checked,
//                        authenticatedUser.ID);
//                    }
//                    else
//                    {
//                        int queryId = Int32.Parse(txtQueryInternalName.Tag.ToString());
//                        int userId = this.authenticatedUser.ID;
//                        success = Querying.UpdateQueryDetails(queryId, name, description, internalName, definition, userId);
//                    }
//#endif
//                    if (success)
//                    {
//                        MessageBox.Show(this, "Query Saved Successfully", AppConfiguration.ApplicationName,
//                            MessageBoxButtons.OK, MessageBoxIcon.Information);
//                        return;
//                    }
//                    else
//                    {
//                        MessageBox.Show(this, "Query was not saved Successfully", AppConfiguration.ApplicationName,
//                            MessageBoxButtons.OK, MessageBoxIcon.Warning);
//                        return;
//                    }
//                }
//                else   // Straight away query
//                {
//                    bool success = false;
//                    if (addingQuery)
//                    {
//#if USING_ENTITIES
//                        Models.EntityContext context = Models.EntityContext.DefaultContext;
//                        Models.Query query = new Models.Query();
//                        query.Name = name;
//                        query.Description = description;
//                        query.Definition = definition;
//                        query.InternalName = internalName;
//                        context.Queries.Add(query);
//                        success = context.SaveChanges() == 0 ? false : true;
//                        if (success) _QueryManagerAllQueries.Add(query);
//#else
//                        success = Querying.SaveQuery(name,
//                            description,
//                            definition,
//                            internalName,
//                            TypeOfQuery.VIEW,
//                            chbCreateTable.Checked,
//                            authenticatedUser.ID);
//#endif
//                    }
//                    else // we are updating existing query
//                    {
//#if USING_ENTITIES
//                        Models.EntityContext context = Models.EntityContext.DefaultContext;
//                        _QueryManagerSelectedQuery.Name = name;
//                        _QueryManagerSelectedQuery.Description = description;
//                        _QueryManagerSelectedQuery.InternalName = internalName;
//                        _QueryManagerSelectedQuery.Definition = definition;
//                        success = context.SaveChanges() == 0 ? false : true;
//#else
//                        int queryId = Int32.Parse(txtQueryInternalName.Tag.ToString());
//                        int userId = this.authenticatedUser.ID;
//                        success = Querying.UpdateQueryDetails(queryId, name, description, internalName, definition, userId);
//#endif
//                    }
//                    if (success)
//                    {
//                        MessageBox.Show(this, "Query Saved Successfully",
//                            AppConfiguration.ApplicationName,
//                            MessageBoxButtons.OK, MessageBoxIcon.Information);
//                    }
//                    else
//                    {
//                        MessageBox.Show(this, "Operation did not complete successfully, Query was not saved",
//                            AppConfiguration.ApplicationName,
//                            MessageBoxButtons.OK, MessageBoxIcon.Error);
//                    }

//                    if (chbCreateTable.Checked)
//                    {
//                        if (definition.Contains("@"))
//                        {
//                            MessageBox.Show(this, "Cannot convert parameterized queries into table.",
//                                AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
//                        }
//                        else
//                        {
//                            success = Querying.CreateTableFromView(internalName, definition);
//                            if (success)
//                            {
//                                MessageBox.Show(this, "Table created successfully",
//                                    AppConfiguration.ApplicationName,
//                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
//                            }
//                            else
//                            {
//                                MessageBox.Show(this, "Table was not created successfully",
//                                    AppConfiguration.ApplicationName,
//                                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
//                            }
//                        }
//                    }
//                }
//            }
//            catch (Exception ex)
//            {
//                MessageBox.Show(this, ex.Message,
//                    AppConfiguration.ApplicationName,
//                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
//            }
        }

        bool tabPageQueryCategorizationLoaded = false;
        private void tabPageQueryCategorization_Enter(object sender, EventArgs e)
        {
            if (!tabPageQueryCategorizationLoaded)
            {
                QueryManagement queryManagement = elementHostQueryManager.Child as QueryManagement;
                if (queryManagement != null)
                {
                    try
                    {
                        queryManagement.GetReportQueriesAndCategories(this.authenticatedUser.ID);
                        queryManagement.QueryDesigner = this;
                        tabPageQueryCategorizationLoaded = true;
                    }
                    catch (Exception ex)
                    {
                        DisplayErrorMessage(ex);
                    }
                }
            }
        }
    }
}
